﻿Imports System.Data.OleDb
Imports util
Imports System.IO

Public Class EvalCursoInstructorService

    Dim sqlCreateEvalCurso As String = "CREATE TABLE Eval(" & _
                                "aula numeric," & _
                                "p11 numeric," & _
                                "p11Obs memo, " & _
                                "p12 numeric," & _
                                "p12Obs memo, " & _
                                "p13 numeric," & _
                                "p13Obs memo, " & _
                                "p14 numeric," & _
                                "p14Obs memo, " & _
                                "p15 numeric," & _
                                "p15Obs memo, " & _
                                "p16 numeric," & _
                                "p16Obs memo, " & _
                                "p17 numeric," & _
                                "p17Obs memo, " & _
                                "p18 numeric," & _
                                "p18Obs memo, " & _
                                "p19 numeric," & _
                                "p19Obs memo, " & _
                                "p21 numeric," & _
                                "p21Obs memo, " & _
                                "p22 numeric," & _
                                "p22Obs memo, " & _
                                "p23 numeric," & _
                                "p23Obs memo, " & _
                                "p24 numeric," & _
                                "p24Obs memo, " & _
                                "p25 numeric," & _
                                "p25Obs memo, " & _
                                "p26 numeric," & _
                                "p26Obs memo, " & _
                                "p27 numeric," & _
                                "p27Obs memo, " & _
                                "p28 numeric," & _
                                "p28Obs memo, " & _
                                "p31 numeric," & _
                                "p31Obs memo, " & _
                                "p32 numeric," & _
                                "p32Obs memo, " & _
                                "p33 numeric," & _
                                "p33Obs memo, " & _
                                "p34 numeric," & _
                                "p34Obs memo, " & _
                                "p35 numeric," & _
                                "p35Obs memo," & _
                                "p36 numeric," & _
                                "p36Obs memo " & _
                                ");"

    Dim sqlCreateEvalInstructor As String = "CREATE TABLE Eval(" & _
                                "instructor text," & _
                                "aula numeric," & _
                                "p1 numeric," & _
                                "p2 numeric," & _
                                "p3 numeric," & _
                                "p4 numeric," & _
                                "p5 numeric," & _
                                "p6 numeric," & _
                                "p7 numeric," & _
                                "p8 numeric," & _
                                "p9 numeric," & _
                                "p10 numeric," & _
                                "p11 numeric," & _
                                "p12 numeric," & _
                                "p13 numeric," & _
                                "p14 numeric," & _
                                "p14_1_Nom text," & _
                                "p14_1_Ape text," & _
                                "p14_2_Nom text," & _
                                "p14_2_Ape text," & _
                                "nom_Instructor text" & _
                                ");"

    Public Function findRegistrosEvaluacionCurso(ByVal con As OleDbConnection) As List(Of Registro)
        Return findRegistrosEvaluacion(con)
    End Function

    Private Function findRegistrosEvaluacion(ByVal con As OleDbConnection) As List(Of Registro)
        Dim lista As New List(Of Registro)

        Dim cmd As New OleDbCommand()
        cmd.Connection = con

        Try
            con.Open()
            cmd.CommandText = "select * from Eval"
            Dim dr As OleDbDataReader = cmd.ExecuteReader()

            While dr.Read
                Dim r As New Registro

                For i = 0 To dr.FieldCount - 1
                    Dim nombre As String = dr.GetName(i)

                    If nombre = "id" Then
                        Continue For
                    End If

                    Dim tipo1 As String = dr.GetDataTypeName(i)
                    Dim tipo2 As String = dr.GetFieldType(i).FullName

                    If IsDBNull(dr(i)) Then
                        r.agregar(nombre, "")
                    Else
                        If tipo2.Contains("String") Then
                            r.agregar(nombre, dr(i).ToString(), True)
                        Else
                            r.agregar(nombre, dr(i).ToString(), False)
                        End If
                    End If
                Next
                lista.Add(r)
            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        Finally
            con.Close()
        End Try

        Return lista
    End Function



    Public Sub consolidarEvaluacionCurso(ByRef rutaArchivos As String, ByVal fileDestinoMdb As String)
        Dim filesAConsolidar As String()
        filesAConsolidar = Directory.GetFiles(rutaArchivos, "EvalCurso*.mdb")
        'For Each file As String In files
        '    MsgBox(file)
        'Next

        Dim con_mdb = MdbUtil.getConnection(fileDestinoMdb, True, "") ' sin clave

        Dim cmd As New OleDbCommand()
        cmd.Connection = con_mdb
        Try
            con_mdb.Open()
            cmd.CommandText = sqlCreateEvalCurso

            cmd.ExecuteNonQuery()

            For Each file In filesAConsolidar
                Dim con_origen As OleDbConnection = MdbUtil.getConnection(file, False, contexto.CLAVE)
                Dim lista As List(Of Registro) = findRegistrosEvaluacionCurso(con_origen)

                For Each r As Registro In lista
                    Dim s As New SentenciaSql("Eval", r)

                    If r.getValores().Count > 0 Then
                        cmd.CommandText = s.getInsertSql()
                        'MsgBox(cmd.CommandText)
                        cmd.ExecuteNonQuery()
                    End If
                Next

            Next

            MsgBox(filesAConsolidar.Length & " archivos consolidados")

        Catch ex As Exception
            MsgBox(ex.ToString)
        Finally
            con_mdb.Close()
        End Try
    End Sub

    Public Function findRegistrosEvaluacionInstructor(ByVal con As OleDbConnection) As List(Of Registro)
        Return findRegistrosEvaluacion(con)
    End Function
    Public Sub consolidarEvaluacionInstructor(ByRef rutaArchivos As String, ByVal fileDestinoMdb As String)
        Dim filesAConsolidar As String()
        filesAConsolidar = Directory.GetFiles(rutaArchivos, "EvalInstructor*.mdb")

        Dim con_mdb = MdbUtil.getConnection(fileDestinoMdb, True, "") ' sin clave

        Dim cmd As New OleDbCommand()
        cmd.Connection = con_mdb
        Try
            con_mdb.Open()
            cmd.CommandText = sqlCreateEvalInstructor

            cmd.ExecuteNonQuery()

            For Each file In filesAConsolidar
                Dim con_origen As OleDbConnection = MdbUtil.getConnection(file, False, contexto.CLAVE)
                Dim lista As List(Of Registro) = findRegistrosEvaluacionInstructor(con_origen)

                For Each r As Registro In lista
                    Dim s As New SentenciaSql("Eval", r)

                    If r.getValores().Count > 0 Then
                        cmd.CommandText = s.getInsertSql()
                        cmd.ExecuteNonQuery()
                    End If
                Next

            Next

            MsgBox(filesAConsolidar.Length & " archivos consolidados")

        Catch ex As Exception
            MsgBox(ex.ToString)
        Finally
            con_mdb.Close()
        End Try
    End Sub
End Class
